USE [Test_GCCSWarehouse]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPopulateDataWarehouse]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spPopulateDataWarehouse]
GO


CREATE PROCEDURE [dbo].[spPopulateDataWarehouse]
	@ChangedSince DATETIME,
	@GCCSDatabaseName VARCHAR(MAX),
	@RPDatabaseName VARCHAR(MAX),
	@EventsDatabaseName VARCHAR(MAX)
AS
BEGIN TRY
	SET NOCOUNT ON

	DECLARE @RPLinkedServer nvarchar(max) = 'DATA-SRV-01' -- 'LOCAL-SRV'
	DECLARE @EventsLinkedServer nvarchar(max) = 'DATA-SRV-03' -- 'LOCAL-SRV'
	DECLARE @GCCSDatabase VARCHAR(MAX)
	--DECLARE @RPDatabase VARCHAR(MAX)
	DECLARE @EventsDatabase VARCHAR(MAX)
	DECLARE @CorporateDataDatabaseName VARCHAR(MAX) = 'Test_CorporateData'

	IF @ChangedSince IS NULL
		BEGIN
			SET @ChangedSince = '2016-09-02'
		END

	--IF @RPDatabaseName IS NULL
	--	BEGIN
	--		SET @RPDatabaseName = 'DSRSystem'
	--	END

	--SET @RPDatabase = '[' + @RPLinkedServer + '].' + @RPDatabaseName
	
	IF @EventsDatabaseName IS NULL
		BEGIN
			SET @EventsDatabaseName = 'Test_Events'
		END
	
	SET @EventsDatabase = @EventsDatabaseName

	IF @GCCSDatabaseName IS NULL
		BEGIN
			SET @GCCSDatabaseName = 'Test_GCCS'
		END

	SET @GCCSDatabase = @GCCSDatabaseName

	--EXECUTE [master].sys.sp_serveroption @server = @RPLinkedServer, @optname = 'remote proc transaction promotion', @optvalue = 'false' ;
	EXECUTE [master].sys.sp_serveroption @server = @EventsLinkedServer, @optname = 'remote proc transaction promotion', @optvalue = 'false' ;

	DECLARE @TotalTime DATETIME
	DECLARE @StartTime DATETIME
	DECLARE @EndTime DATETIME
	DECLARE @RowsAffected INT
	DECLARE @ElapsedTime INT

	SELECT @TotalTime = GETDATE()
		
	DECLARE @CurrentQuarter DATETIME
	DECLARE @IsClosed BIT
	DECLARE @IsLastSynchronizationDone BIT

	DECLARE @SQL NVARCHAR(MAX) = 'SELECT TOP (1) @CurrentQuarter = Date, @IsClosed = IsClosed, @IsLastSynchronizationDone=IsLastSynchronizationDone FROM ' + @GCCSDatabase + '.dbo.GccsCurrentQuarter ORDER BY Date DESC'
	DECLARE @ParameterDefinition NVARCHAR(MAX) = '@CurrentQuarter DATETIME OUTPUT, @IsClosed BIT OUTPUT, @IsLastSynchronizationDone BIT OUTPUT'
	EXEC sp_executeSQL @SQL, @ParameterDefinition, @CurrentQuarter OUTPUT, @IsClosed OUTPUT, @IsLastSynchronizationDone OUTPUT;

	IF @IsClosed = 1 AND @IsLastSynchronizationDone = 1
	    RAISERROR ('The current quarter is closed.', 18, 1);

	DECLARE @CurrentQuartertText NVARCHAR(MAX)
	SET @CurrentQuartertText = 'Q' + CONVERT(VARCHAR(1), Datepart(qq, @CurrentQuarter)) + ' ' + CONVERT(VARCHAR(4), Datepart(yyyy, @CurrentQuarter))
	PRINT 'Current quarter: ' + @CurrentQuartertText + ' (' + FORMAT(@CurrentQuarter, 'dd-MM-yyyy') + ')'

	DECLARE @ChangedSinceText NVARCHAR(MAX)
	DECLARE @CurrentQuarterText NVARCHAR(MAX)
	
	SET @ChangedSinceText = FORMAT(@ChangedSince, 'yyyy-MM-dd')
	SET @CurrentQuarterText = FORMAT(@CurrentQuarter, 'yyyy-MM-dd')

	TRUNCATE TABLE [dbo].[Pillars]
	INSERT INTO [dbo].[Pillars]
           ([Id]
           ,[Name])
	EXEC('SELECT Id, Name FROM ' + @GCCSDatabase + '.dbo.GccsPillarTemplates ORDER BY Id')

	-- Get GCCS Company Profiles -------------------------

	SELECT @StartTime = GETDATE()

	DECLARE @CompanyProfiles TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		CompanyName NVARCHAR(200) NOT NULL,
		CompanyDescription NVARCHAR(MAX) NOT NULL,
		Country NVARCHAR(50) NOT NULL,
		ShortPeerGroupName NVARCHAR(50) NOT NULL,
		MainISIN NVARCHAR(50) NULL,
		MainSedol NVARCHAR(50) NULL,
		MainTicker NVARCHAR(50) NULL,
		LastCorporateDataUpdate DATETIME NOT NULL
	);
	INSERT INTO @CompanyProfiles
	EXEC (@CorporateDataDatabaseName + '.dbo.spGetCompanyProfiles')
	
	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Get company profiles from RPCorporateData database, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	SELECT @StartTime = GETDATE()

	DECLARE @GCCSCompanyProfiles TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		GCSignatorySince DATETIME NULL,
		OverallCompanyAssessment INT NOT NULL,
		OverallCompanyAssessmentEffectiveSince DATETIME NOT NULL,
		LastUpdated DATETIME NOT NULL
	);
	INSERT INTO @GCCSCompanyProfiles
	EXEC(@GCCSDatabase + '.dbo.spGetCompanyProfiles ''' + @ChangedSinceText + '''')
	--select * from @GccsCompanyProfiles

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Get GCCS company profiles from GCCS database, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	SELECT @StartTime = GETDATE()


	DECLARE @ReviewedCompanyProfiles TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		Quarter DATETIME NOT NULL,
		CompanyName NVARCHAR(200) NOT NULL,
		CompanyDescription NVARCHAR(MAX) NOT NULL,
		Country NVARCHAR(50) NOT NULL,
		GCSignatorySince DATETIME NULL,
		MainISIN NVARCHAR(50) NULL,
		MainSedol NVARCHAR(50) NULL,
		OverallCompanyAssessment INT NOT NULL,
		ShortPeerGroupName NVARCHAR(50) NOT NULL,
		CompanyProfileLastUpdate DATETIME NOT NULL
	);
	INSERT INTO @ReviewedCompanyProfiles
	SELECT	CompanyProfiles.CompanyId,
			@CurrentQuarter AS Quarter,
			CompanyProfiles.CompanyName,
			CompanyProfiles.CompanyDescription,
			CompanyProfiles.Country,
			GCCSCompanyProfiles.GCSignatorySince,
			CompanyProfiles.MainISIN,
			CompanyProfiles.MainSedol,
			GCCSCompanyProfiles.OverallCompanyAssessment,
			CompanyProfiles.ShortPeerGroupName,
			CASE WHEN ISNULL(CompanyProfiles.LastCorporateDataUpdate, GCCSCompanyProfiles.OverallCompanyAssessmentEffectiveSince) > ISNULL(GCCSCompanyProfiles.OverallCompanyAssessmentEffectiveSince, CompanyProfiles.LastCorporateDataUpdate)
            THEN ISNULL(CompanyProfiles.LastCorporateDataUpdate, GCCSCompanyProfiles.OverallCompanyAssessmentEffectiveSince)
            ELSE ISNULL(GCCSCompanyProfiles.OverallCompanyAssessmentEffectiveSince, CompanyProfiles.LastCorporateDataUpdate) END AS CompanyProfileLastUpdate
	FROM @CompanyProfiles AS CompanyProfiles
	INNER JOIN @GCCSCompanyProfiles AS GCCSCompanyProfiles ON CompanyProfiles.CompanyId = GCCSCompanyProfiles.CompanyId
	
	--select * from @ReviewedCompanyProfiles as ReviewedCompanyProfiles where OverallCompanyAssessment = 1 OR OverallCompanyAssessment = 2

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Reviewed company profiles, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	SELECT @StartTime = GETDATE()

	DECLARE @ActualCompanyProfiles TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		Quarter DATETIME NOT NULL,
		OverallCompanyAssessment INT NOT NULL,
		CompanyProfileLastUpdate DATETIME NOT NULL
	);
	INSERT INTO @ActualCompanyProfiles
	SELECT CompanyId, Quarter, OverallCompanyAssessment, CompanyProfileLastUpdate
	FROM [dbo].[OverallSummarySections]
	WHERE Quarter = @CurrentQuarter

	--select * from @ActualCompanyProfiles as ActualCompanyProfiles

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Get actual company profiles, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	SELECT @StartTime = GETDATE()

	DECLARE @Changes TABLE
	(
		Change NVARCHAR(10), 
		CompanyId UNIQUEIDENTIFIER,
		Quarter DATETIME
	);

	MERGE @ActualCompanyProfiles AS TargetTable
	USING (SELECT CompanyId, Quarter, OverallCompanyAssessment, CompanyProfileLastUpdate FROM @ReviewedCompanyProfiles WHERE OverallCompanyAssessment = 1 OR OverallCompanyAssessment = 2) AS SourceTable
	ON (TargetTable.CompanyId = SourceTable.CompanyId) AND (TargetTable.Quarter = SourceTable.Quarter) 
	WHEN NOT MATCHED BY TARGET THEN 
		INSERT (CompanyId, Quarter, OverallCompanyAssessment, CompanyProfileLastUpdate) 
		VALUES (SourceTable.CompanyId, SourceTable.Quarter, SourceTable.OverallCompanyAssessment, SourceTable.CompanyProfileLastUpdate)
	--WHEN MATCHED AND (TargetTable.CompanyProfileLastUpdate <> SourceTable.CompanyProfileLastUpdate) THEN 
	WHEN MATCHED THEN 
		UPDATE SET TargetTable.CompanyProfileLastUpdate = SourceTable.CompanyProfileLastUpdate
	OUTPUT $action, 
		CASE WHEN $action='INSERT' THEN Inserted.CompanyId ELSE SourceTable.CompanyId END AS CompanyId,
		CASE WHEN $action='INSERT' THEN Inserted.Quarter ELSE SourceTable.Quarter END AS Quarter
	INTO @Changes;


	MERGE @ReviewedCompanyProfiles AS TargetTable
	USING @ActualCompanyProfiles AS SourceTable
	ON (TargetTable.CompanyId = SourceTable.CompanyId) AND (TargetTable.Quarter = SourceTable.Quarter) 
	WHEN MATCHED AND (TargetTable.OverallCompanyAssessment <> SourceTable.OverallCompanyAssessment AND TargetTable.OverallCompanyAssessment = 0) 
		THEN DELETE
	OUTPUT $action, 
		CASE WHEN $action='DELETE' THEN Deleted.CompanyId ELSE SourceTable.CompanyId END AS CompanyId,
		CASE WHEN $action='DELETE' THEN Deleted.Quarter ELSE SourceTable.Quarter END AS Quarter
	INTO @Changes;

	--select * from @Changes

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'What changes?, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'


	SELECT @StartTime = GETDATE()

	DELETE ReportsCaches
	FROM [dbo].[ReportsCaches] AS ReportsCaches 
	INNER JOIN @Changes AS ChangedCompanyProfiles ON ReportsCaches.CompanyId = ChangedCompanyProfiles.CompanyId AND ReportsCaches.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'DELETE'

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Delete reports caches, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'


	SELECT @StartTime = GETDATE()

	DELETE OverallSummarySections
	FROM [dbo].[OverallSummarySections] AS OverallSummarySections 
	INNER JOIN @Changes AS ChangedCompanyProfiles ON OverallSummarySections.CompanyId = ChangedCompanyProfiles.CompanyId AND OverallSummarySections.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'DELETE' OR ChangedCompanyProfiles.Change = 'UPDATE'

	INSERT INTO [dbo].[OverallSummarySections] (CompanyId, Quarter, CompanyName, CompanyDescription, Country, GCSignatorySince, MainISIN, MainSedol, OverallCompanyAssessment, ShortPeerGroupName, CompanyProfileLastUpdate)
	SELECT OverallSummarySections.CompanyId, OverallSummarySections.Quarter, CompanyName, CompanyDescription, Country, GCSignatorySince, MainISIN, MainSedol, OverallCompanyAssessment, ShortPeerGroupName, CompanyProfileLastUpdate
	FROM @ReviewedCompanyProfiles AS OverallSummarySections
	INNER JOIN @Changes AS ChangedCompanyProfiles ON OverallSummarySections.CompanyId = ChangedCompanyProfiles.CompanyId AND OverallSummarySections.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'INSERT' OR ChangedCompanyProfiles.Change = 'UPDATE'

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Delete/Insert company profiles, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	SELECT @StartTime = GETDATE()
		
	DECLARE @PrinciplesSummarySections TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		PrincipleTemplateId INT NOT NULL,
		Quarter DATETIME NOT NULL,
		Issue NVARCHAR(max) NULL,
		PillarId INT NOT NULL,
		PrincipleAssessment INT NOT NULL,
		PrincipleAssessmentEffectiveSince DATETIME NOT NULL,
		PrincipleGccsName NVARCHAR(50) NOT NULL,
		PrincipleGirName NVARCHAR(50) NOT NULL,
		CompanyName NVARCHAR(200) NOT NULL,
		AssessmentSummaryAndConclusionIntroductionSentence NVARCHAR(300) NOT NULL,
		AssessmentComment1 NVARCHAR(1200) NULL,
		AssessmentComment2 NVARCHAR(1200) NULL,
		AssessmentComment3 NVARCHAR(1200) NULL,
		AssessmentComment4 NVARCHAR(1200) NULL,
		AssessmentComment5 NVARCHAR(1200) NULL,
		AssessmentComment6 NVARCHAR(1200) NULL,
		PrincipleGccsDescription NVARCHAR(1500) NOT NULL,
		PrincipleGirDescription NVARCHAR(1500) NOT NULL
	)
	INSERT INTO @PrinciplesSummarySections
	EXEC(@GCCSDatabase + '.dbo.spGetPrinciples ''' + @ChangedSinceText + ''', ''' + @CurrentQuarterText + '''')
	--select * from @PrinciplesSummarySections

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Get principles summary, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	SELECT @StartTime = GETDATE()

	DELETE PrinciplesSummarySections
	FROM [dbo].[PrinciplesSummarySections] AS PrinciplesSummarySections
	INNER JOIN @Changes AS ChangedCompanyProfiles ON PrinciplesSummarySections.CompanyId = ChangedCompanyProfiles.CompanyId AND PrinciplesSummarySections.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'DELETE' OR ChangedCompanyProfiles.Change = 'UPDATE'

	INSERT INTO [dbo].[PrinciplesSummarySections] (CompanyId, PrincipleTemplateId, Quarter, Issue, PillarId, PrincipleAssessment, PrincipleAssessmentEffectiveSince, PrincipleGccsName, PrincipleGirName, CompanyName, AssessmentSummaryAndConclusionIntroductionSentence, AssessmentComment1, AssessmentComment2, AssessmentComment3, AssessmentComment4, AssessmentComment5, AssessmentComment6, PrincipleGccsDescription, PrincipleGirDescription)
	SELECT PrinciplesSummarySections.CompanyId, PrincipleTemplateId, PrinciplesSummarySections.Quarter, Issue, PillarId, PrincipleAssessment, PrincipleAssessmentEffectiveSince, PrincipleGccsName, PrincipleGirName, CompanyProfiles.CompanyName, AssessmentSummaryAndConclusionIntroductionSentence, AssessmentComment1, AssessmentComment2, AssessmentComment3, AssessmentComment4, AssessmentComment5, AssessmentComment6, PrincipleGccsDescription, PrincipleGirDescription
	FROM @PrinciplesSummarySections AS PrinciplesSummarySections
	INNER JOIN @CompanyProfiles AS CompanyProfiles ON PrinciplesSummarySections.CompanyId = CompanyProfiles.CompanyId
	INNER JOIN @Changes AS ChangedCompanyProfiles ON PrinciplesSummarySections.CompanyId = ChangedCompanyProfiles.CompanyId AND PrinciplesSummarySections.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'INSERT' OR ChangedCompanyProfiles.Change = 'UPDATE'

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Delete/Insert principles summary, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	SELECT @StartTime = GETDATE()

	DECLARE @PrinciplesDetailsSections TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		PrincipleTemplateId INT NOT NULL,
		Quarter DATETIME NOT NULL,
		BaseEventIndicatorId UNIQUEIDENTIFIER NULL,
		EventIndicatorAssessment INT NOT NULL,
		EventSummary NVARCHAR(1500) NOT NULL,
		ImpactSummary NVARCHAR(300) NOT NULL,
		ImpactComment1 NVARCHAR(1200) NULL,
		ImpactComment2 NVARCHAR(1200) NULL,
		ImpactComment3 NVARCHAR(1200) NULL,
		ImpactComment4 NVARCHAR(1200) NULL,
		ImpactComment5 NVARCHAR(1200) NULL,
		IssueType NVARCHAR(250) NOT NULL,
		ManagementSummary NVARCHAR(300) NOT NULL,
		ManagementComment1 NVARCHAR(1200) NULL,
		ManagementComment2 NVARCHAR(1200) NULL,
		ManagementComment3 NVARCHAR(1200) NULL,
		ManagementComment4 NVARCHAR(1200) NULL,
		ManagementComment5 NVARCHAR(1200) NULL,
		PillarId INT NOT NULL,
		PrincipleAssessment INT NOT NULL,
		PrincipleGccsName NVARCHAR(50) NOT NULL,
		PrincipleGirName NVARCHAR(50) NOT NULL
	)
	INSERT INTO @PrinciplesDetailsSections
	EXEC(@GCCSDatabase + '.dbo.spGetPrinciplesDetails ''' + @ChangedSinceText + ''', ''' + @CurrentQuarterText + '''')
	--select * from @PrinciplesDetailsSections

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Get principles details, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	
	SELECT @StartTime = GETDATE()

	DELETE [dbo].[PrinciplesDetailsSections]
	FROM [dbo].[PrinciplesDetailsSections] AS PrinciplesDetailsSections
	INNER JOIN @Changes AS ChangedCompanyProfiles ON PrinciplesDetailsSections.CompanyId = ChangedCompanyProfiles.CompanyId AND PrinciplesDetailsSections.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'DELETE' OR ChangedCompanyProfiles.Change = 'UPDATE'


	INSERT INTO [dbo].[PrinciplesDetailsSections] (CompanyId, PrincipleTemplateId, Quarter, EventIndicatorAssessment, EventSummary,  ImpactSummary, ImpactComment1, ImpactComment2, ImpactComment3, ImpactComment4, ImpactComment5, IssueType, ManagementSummary, ManagementComment1, ManagementComment2, ManagementComment3, ManagementComment4, ManagementComment5, PillarId, PrincipleAssessment, PrincipleGccsName, PrincipleGirName)
	SELECT PrinciplesDetailsSections.CompanyId, PrincipleTemplateId, PrinciplesDetailsSections.Quarter, EventIndicatorAssessment, EventSummary,  ImpactSummary, ImpactComment1, ImpactComment2, ImpactComment3, ImpactComment4, ImpactComment5, IssueType, ManagementSummary, ManagementComment1, ManagementComment2, ManagementComment3, ManagementComment4, ManagementComment5, PillarId, PrincipleAssessment, PrincipleGccsName, PrincipleGirName
	FROM @PrinciplesDetailsSections AS PrinciplesDetailsSections
	INNER JOIN @Changes AS ChangedCompanyProfiles ON PrinciplesDetailsSections.CompanyId = ChangedCompanyProfiles.CompanyId AND PrinciplesDetailsSections.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'INSERT' OR ChangedCompanyProfiles.Change = 'UPDATE'

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Delete/Insert principles details, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'

	SELECT @StartTime = GETDATE()

	DECLARE @EventIndicators TABLE
	(
		c UNIQUEIDENTIFIER NOT NULL,
		bei UNIQUEIDENTIFIER NOT NULL,
		eia INT NOT NULL
	)
	INSERT INTO @EventIndicators
	SELECT CompanyId, BaseEventIndicatorId, EventIndicatorAssessment FROM @PrinciplesDetailsSections
	WHERE BaseEventIndicatorId IS NOT NULL
	
	DECLARE @SelectedEventIndicators NVARCHAR(MAX)
	DECLARE @XML XML
	SET @XML = (SELECT * FROM @EventIndicators FOR XML RAW('ei'), ROOT('eis'), ELEMENTS, TYPE)
	SET @SelectedEventIndicators = CONVERT(NVARCHAR(MAX), @XML, 1)

	DECLARE @SourcesSections TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		Quarter DATETIME NOT NULL,
		Type INT NOT NULL,
		EventIndicatorAssessment INT NOT NULL,
		PublicationDate DATETIME NOT NULL,
		NewsArticleName NVARCHAR(450) NOT NULL,
		SourceName NVARCHAR(450) NULL
	)

	INSERT INTO @SourcesSections
	EXEC(@EventsDatabase + '.dbo.spGetNewsArticleSources ''' + @SelectedEventIndicators + ''', ''' + @CurrentQuarterText + '''')
	
	DELETE dbo.SourcesSections
	FROM dbo.SourcesSections AS SourcesSections
	INNER JOIN @Changes AS ChangedCompanyProfiles ON SourcesSections.CompanyId = ChangedCompanyProfiles.CompanyId AND SourcesSections.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'DELETE' OR ChangedCompanyProfiles.Change = 'UPDATE'

	INSERT INTO dbo.SourcesSections (CompanyId, Quarter, PublicationDate, NewsArticleName, SourceName, Type, EventIndicatorAssessment)
	SELECT SourcesSections.CompanyId, SourcesSections.Quarter, PublicationDate, NewsArticleName, SourceName, Type, EventIndicatorAssessment
	FROM @SourcesSections AS SourcesSections
	INNER JOIN @Changes AS ChangedCompanyProfiles ON SourcesSections.CompanyId = ChangedCompanyProfiles.CompanyId AND SourcesSections.Quarter = ChangedCompanyProfiles.Quarter
	WHERE ChangedCompanyProfiles.Change = 'INSERT' OR ChangedCompanyProfiles.Change = 'UPDATE'

	SET @RowsAffected = @@ROWCOUNT
	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @StartTime, @EndTime)
	PRINT 'Delete/Insert sources, RowsAffected: ' + LTRIM(STR(@RowsAffected)) + ', ElapsedTime: ' + LTRIM(STR(@ElapsedTime)) + 's'


	SELECT @EndTime = GETDATE()
	SELECT @ElapsedTime = DATEDIFF(SECOND, @TotalTime, @EndTime)
	PRINT 'Populate warehouse elapsed time: ' + LTRIM(STR(@ElapsedTime)) + 's'

END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH;
RETURN 0


GO


